﻿﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
///sqlhelp 的摘要说明
/// </summary>
public class sqlhelp
{
    //数据库连接串设置
    public static readonly string connString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

    public SqlConnection myConn = new SqlConnection(connString);

    /// <summary>
    /// 打开数据库连接.
    /// </summary>
    public void Open()
    {
        if (myConn == null)
        {
            //如果myConn没有实例化，则新建个对象
            myConn = new SqlConnection(connString);
            myConn.Open();
        }
        else if (myConn.State == System.Data.ConnectionState.Closed)
        {
            //如果myConn关闭，则打开
            myConn.Open();
        }
        else if (myConn.State == System.Data.ConnectionState.Broken)
        {
            //如果myConn中断，则关闭后，再打开
            myConn.Close();
            myConn.Open();
        }

    }

    /// <summary>
    /// 为SqlCommand.CommandText赋值
    /// </summary>
    /// <param name="cmd">SqlCommand对象</param>
    /// <param name="sql">sql语句</param>
    /// <param name="parameters">sql语句参数</param>
    public void setSqlCommand(SqlCommand cmd, string sqlString, SqlParameter[] parameters)
    {
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                cmd.Parameters.Add(parameter);
            }
        }
    }

    /// <summary>
    /// 得到数据集
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <param name="parameters">sql语句参数</param>
    /// <returns>数据集</returns>
    public DataSet GetDataSet(string sqlString, SqlParameter[] parameters)
    {
        //打开数据库
        Open();

        SqlCommand cmd = new SqlCommand(sqlString, myConn);
        setSqlCommand(cmd, sqlString, parameters);
        SqlDataAdapter da = new SqlDataAdapter(cmd);


        DataSet ds = new DataSet();
        try
        {
            //把适配器中的查询结果填充到ds中表名为tableName
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            //关闭数据库
            // myConn.Close();
        }
        return ds;
    }

    /// <summary>
    /// 得到数据集
    /// </summary>
    /// <param name="tableName">DataSet中的表名</param>
    /// <param name="sqlString">sql语句</param>
    /// <param name="parameters">sql语句参数</param>
    /// <returns>数据集</returns>
    public DataSet GetDataSet(string tableName, string sqlString, SqlParameter[] parameters)
    {

        //打开数据库
        Open();

        //创建适配器对象
        SqlCommand cmd = new SqlCommand(sqlString, myConn);
        setSqlCommand(cmd, sqlString, parameters);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        try
        {
            //把适配器中的查询结果填充到ds中表名为tableName
            sda.Fill(ds);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            //关闭数据库
            // myConn.Close();
        }
        return ds;
    }

    /// <summary>
    /// 受影响的行数，执行成功返回1，失败返回0
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <returns>0或1</returns>
    public int GetNonQuery(string sqlString, SqlParameter[] parameters)
    {
        //val用来返回值
        int val = 0;

        //打开数据库
        Open();
        //声明要执行的对象
        SqlCommand cmd = new SqlCommand(sqlString, myConn);
        setSqlCommand(cmd, sqlString, parameters);
        try
        {
            val = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            //关闭数据库
            myConn.Close();
        }
        return val;
    }




    /// <summary>
    /// 受影响的行数，执行成功返回1，失败返回0
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <returns>0或1</returns>
    public bool GetNonQuery1(string sqlString, SqlParameter[] parameters)
    {
        //val用来返回值
        bool val = false;

        //声明要执行的对象
        SqlCommand cmd = new SqlCommand(sqlString, myConn);
        setSqlCommand(cmd, sqlString, parameters);
        //打开数据库
        Open();
        try
        {
            cmd.ExecuteNonQuery();
            val = true;
        }
        catch (Exception ex)
        {
            //throw new Exception(ex.ToString());
        }
        finally
        {
            //关闭数据库
            myConn.Close();
        }
        return val;
    }

    /// <summary>
    /// 得到记录行数，执行成功返回1，失败返回0
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <returns>0或一个整数</returns>
    public int GetScalar(string sqlString, SqlParameter[] parameters)
    {
        //val用来返回值
        int val = 0;

        //打开数据库
        Open();
        //声明要执行的对象
        SqlCommand cmd = new SqlCommand(sqlString, myConn);
        setSqlCommand(cmd, sqlString, parameters);
        try
        {
            //试着执行代码，如果出错则跳转到catch
            val = (int)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            //throw new Exception(ex.ToString());
        }
        finally
        {
            //关闭数据库
            myConn.Close();
        }
        return val;
    }

    /// <summary>
    /// 得到记录行数，执行成功返回1，失败返回0
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <returns>0或一个整数</returns>
    public bool ExecuteScalar(string sqlString, SqlParameter[] parameters)
    {
        //val用来返回值
        bool val = false;

        //打开数据库
        Open();
        //声明要执行的对象
        SqlCommand cmd = new SqlCommand(sqlString, myConn);
        setSqlCommand(cmd, sqlString, parameters);
        try
        {
            //试着执行代码，如果出错则跳转到catch
            cmd.ExecuteScalar();
            val = true;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            //关闭数据库
            myConn.Close();
        }
        return val;
    }


    //定义方法为读取数据库的内容

    public DataSet ExecuteDataSet(String tablename, String cmdText)
    {


        SqlDataAdapter da = new SqlDataAdapter(cmdText, myConn);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds, tablename);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            myConn.Close();
        }
        return ds;
    }

    public int ExecuteNonQuery(String cmdText)
    {
        //此方法执行sql，如果执行成功返回1，失败返回0
        int val = 0;
        SqlCommand cmd = new SqlCommand(cmdText, myConn);

        //打开数据库
        try
        {
            Open();
            val = cmd.ExecuteNonQuery();

        }
        catch
        {

        }
        finally
        {
            myConn.Close();
        }
        return val;
    }



    public DataSet myDataSet(string cmdText)
    {
        SqlDataAdapter myDataAdapter = new SqlDataAdapter(cmdText, myConn);
        DataSet myDataSet = new DataSet();
        try
        {
            myDataAdapter.Fill(myDataSet);
        }
        catch
        {

        }
        finally
        {
            myConn.Close();
        }
        return myDataSet;
    }

    //用于返回记录行数
    public int ExecuteScalar(string cmdText)
    {
        //此方法执行sql，如果执行成功返回1，失败返回0
        int val = 0;//val用来返回值
        //声明要执行的对象
        SqlCommand cmd = new SqlCommand(cmdText, myConn);
        //打开数据库
        Open();
        try
        {
            //试着执行代码，如果出错则跳转到catch
            val = (int)cmd.ExecuteScalar();//执行sql语句
        }
        catch
        {
            //throw new Exception(ex.Message, ex);
        }
        finally
        {
            myConn.Close();
        }
        return val;
    }



    #region  权限进行分页

    public int GetCount(string tablename, string where)
    {

        string _sql = "select count(*) from " + tablename + " where 1 = 1 " + where + "";

        int count = 0;
        try
        {
            count = int.Parse(myDataSet(_sql).Tables[0].Rows[0][0].ToString());
        }
        catch (Exception)
        {

            throw;
        }
        return count;

    }

    public DataSet GetPermissionsPager(string sort, string order, int currentPage, int pageSize, string where, string tablename)
    {
        string _sql = @" select  *
                                from (select * ,Row_number() over(order by " + sort + " " + order + ") as IDRank from " + tablename + " WHERE 1 = 1 " + where + ") as IDWithRowNumber where IDRank>=(" + currentPage + "-1)*" + pageSize + "+1 and IDRank<=" + currentPage + "*" + pageSize + "";

        return myDataSet(_sql);
    }

    public DataSet GetPermissionsPagerColumn(string sort, string order, int currentPage, int pageSize, string where, string tablename, string columnname)
    {
        string _sql = @" select  *
                                from (select  " + columnname + ",Row_number() over(order by " + sort + " " + order + ") as IDRank from " + tablename + " WHERE 1 = 1 " + where + ") as IDWithRowNumber where IDRank>=(" + currentPage + "-1)*" + pageSize + "+1 and IDRank<=" + currentPage + "*" + pageSize + "";

        return myDataSet(_sql);
    }

    #endregion
}